﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace ShiQuan.Access.Helper
{
    /// <summary>
    /// 数据库操作业务对象
    /// </summary>
    public class OleDbHelper : IDisposable
    {
        private OleDbConnection mDBconn = null;
        private string _DbFileName = string.Empty;
        /// <summary>
        /// 数据库
        /// </summary>
        public string DBFileName
        {
            get { return this._DbFileName; }
            set { this._DbFileName = value; }
        }
        private string _ConnectionString = string.Empty;

        public OleDbHelper()
        {

        }

        public OleDbHelper(string dbFileName)
        {
            this.DBFileName = dbFileName;
        }


        /// <summary>
        /// 数据连接对象
        /// </summary>
        public OleDbConnection DBConnection
        {
            get
            {
                if (this.mDBconn == null)
                {
                    this._ConnectionString = OleDbHelper.GetConnectionString(this.DBFileName);
                    this.mDBconn = new OleDbConnection(this._ConnectionString);
                }
                return this.mDBconn;
            }
        }
        private List<OleDbParameter> _Parsms = new List<OleDbParameter>();
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void AddParam(string name, object value)
        {
            this._Parsms.Add(new OleDbParameter(name, value));
        }

        /// <summary>
        /// 打开
        /// </summary>
        public void Open()
        {
            if (this.DBConnection != null && this.DBConnection.State == ConnectionState.Closed)
                this.DBConnection.Open();
        }
        /// <summary>
        /// 关闭
        /// </summary>
        private void Close()
        {
            if (this.DBConnection != null && this.DBConnection.State == ConnectionState.Open)
                this.DBConnection.Close();
        }

        #region IDisposable 成员
        /// <summary>
        /// 清除资源
        /// </summary>
        public void Dispose()
        {
            this.Close();
            if (this.mDBconn != null)
            {
                this.mDBconn = null;
            }
        }

        #endregion
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="pCommandText">命令</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string pCommandText)
        {
            int mReturnValue = 0;
            try
            {
                this.Open();
                using (OleDbCommand mCom = new OleDbCommand(pCommandText, this.DBConnection))
                {
                    mCom.CommandType = CommandType.Text;
                    if (_Parsms.Count > 0)
                        mCom.Parameters.AddRange(this._Parsms.ToArray());
                    mReturnValue = mCom.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                //XHTMessageBox.ShowError(ex.Message);
                Logger.Error("执行数据命令异常：" + pCommandText, ex);
                throw ex;
            }
            finally
            {
                this._Parsms.Clear();
                this.Close();
            }
            return mReturnValue;
        }
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="pCommandText"命令></param>
        /// <returns></returns>
        public object ExecuteScalar(string pCommandText)
        {
            object mReturnValue = null;
            try
            {
                this.Open();
                using (OleDbCommand mCom = new OleDbCommand(pCommandText, this.DBConnection))
                {
                    mCom.CommandType = CommandType.Text;
                    if (_Parsms.Count > 0)
                        mCom.Parameters.AddRange(this._Parsms.ToArray());
                    mReturnValue = mCom.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                //XHTMessageBox.ShowError(ex.Message);
                Logger.Error("执行数据命令异常：" + pCommandText, ex);
                throw ex;
            }
            finally
            {
                this._Parsms.Clear();
                this.Close();
            }
            return mReturnValue;
        }

        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="pCommandText"命令></param>
        /// <returns></returns>
        public int Update(string tableName, DataTable dtChanges)
        {
            int mReturnValue = -1;
            try
            {
                this.Open();
                string mCommandText = "Select * from " + tableName;
                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(mCommandText, this.DBConnection))
                {
                    OleDbCommandBuilder buider = new OleDbCommandBuilder(dataAdapter);

                    DataTable dtSource = new DataTable();
                    dataAdapter.Fill(dtSource);

                    mReturnValue = dataAdapter.Update(dtChanges);
                }
            }
            catch (Exception ex)
            {
                //XHTMessageBox.ShowError(ex.Message);
                Logger.Error("执行数据命令异常:" + tableName, ex);
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return mReturnValue;
        }
        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="pCommandText">命令</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string pCommandText)
        {
            DataTable dt = null;
            try
            {
                this.Open();
                using (OleDbDataAdapter mData = new OleDbDataAdapter(pCommandText, this.DBConnection))
                {
                    dt = new DataTable();
                    mData.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                //XHTMessageBox.ShowError(ex.Message);
                Logger.Error("执行数据命令异常：" + pCommandText, ex);
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return dt;
        }

        /// <summary>
        /// 获取数据库所有表
        /// </summary>
        /// <param name="pCommandText">命令</param>
        /// <returns></returns>
        public DataTable GetTables()
        {
            try
            {
                this.Open();
                return this.DBConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            }
            catch (Exception ex)
            {
                //XHTMessageBox.ShowError(ex.Message);
                Logger.Error("获取数据库所有表", ex);
                throw ex;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 获取数据库所有表
        /// </summary>
        /// <param name="pCommandText">命令</param>
        /// <returns></returns>
        public DataTable GetColumns(string tableName)
        {
            DataTable dtColumns = new DataTable();
            dtColumns.Columns.Add("Column_Name");
            dtColumns.Columns.Add("DataType");
            dtColumns.Columns.Add("Description");
            dtColumns.Columns.Add("PrimaryKey");
            dtColumns.Columns.Add("Position", typeof(int));
            try
            {
                this.Open();
                DataTable dtSchema = this.DBConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
                DataView dvPrimary = new DataView(this.DBConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null));

                foreach (DataRow drSchema in dtSchema.Rows)
                {
                    DataRow dr = dtColumns.NewRow();
                    dr["Column_Name"] = drSchema["Column_Name"];
                    dr["Description"] = drSchema["Description"];
                    dr["Position"] = drSchema["Ordinal_Position"];

                    int lx = int.Parse(drSchema["Data_Type"].ToString());
                    switch (lx)
                    {
                        case 2:
                        case 3:
                            {
                                dr["DataType"] = "int"; break;
                            }
                        case 4:
                            {
                                dr["DataType"] = "Single"; break;
                            }
                        case 5:
                            {
                                dr["DataType"] = "double"; break;
                            }
                        case 6:
                        case 131:
                            {
                                dr["DataType"] = "decimal"; break;
                            }
                        case 7:
                            {
                                dr["DataType"] = "DateTime"; break;
                            }
                        case 11:
                            {
                                dr["DataType"] = "bool"; break;
                            }
                        case 17:
                            {
                                dr["DataType"] = "tyte"; break;
                            }
                        default:
                            {
                                dr["DataType"] = "string"; break;
                            }
                    }

                    dvPrimary.RowFilter = "Table_Name='" + tableName + "' and Column_Name='" + dr["Column_Name"].ToString() + "'";
                    dr["PrimaryKey"] = dvPrimary.Count > 0 ? "1" : "0";
                    dtColumns.Rows.Add(dr);
                }
                return dtColumns;
            }
            catch (Exception ex)
            {
                //XHTMessageBox.ShowError(ex.Message);
                Logger.Error("获取数据库所有表", ex);
                throw ex;
            }
            finally
            {
                this.Close();
            }

        }

        /// <summary>
        /// 获取数据库所有表
        /// </summary>
        /// <param name="pCommandText">命令</param>
        /// <returns></returns>
        public DataTable GetSchemaColumns(string tableName)
        {
            try
            {
                this.Open();
                return this.DBConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
            }
            catch (Exception ex)
            {
                //XHTMessageBox.ShowError(ex.Message);
                Logger.Error("获取数据库所有表", ex);
                throw ex;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <param name="dbFileName"></param>
        /// <returns></returns>
        public static string GetConnectionString(string dbFileName)
        {
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(dbFileName);
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFileName;
        }
    }
}
